/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import POJO.KhachHang;
import POJO.KhachHangPhanLoai;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Hồng
 */
public class KhachHangDao {

	public static ArrayList<KhachHang> layDanhSach() {
		ArrayList<KhachHang> ds = new ArrayList<KhachHang>();
		String sql = "SELECT  MaKH  , TenKH, DiaChi, DienThoai   FROM  KhachHang ";

		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				int ma = rs.getInt("MaKH");
				String ten = rs.getString("TenKH");
				String diachi = rs.getString("DiaChi");
				String dienthoai = rs.getString("DienThoai");
				KhachHang tmp = new KhachHang(ma, ten, diachi, dienthoai);
				ds.add(tmp);
			}
		} catch (Exception ex) {
			Logger.getLogger(KhachHangDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}

	public static ArrayList<KhachHangPhanLoai> layDanhSach(Date from, Date to, int soDong) {

		int thangBD = from.getMonth();
		int thangKT = to.getMonth();

		thangBD += 1;//co 12 thang trong 1 nam //thang dau tien = 0
		thangKT += 1;
		String ngayBatDau = from.getYear() + "-" + thangBD + "-" + from.getDate();
		String ngayKetThuc = to.getYear() + "-" + thangKT + "-" + to.getDate();


		ArrayList<KhachHangPhanLoai> ds = new ArrayList<KhachHangPhanLoai>();
		String sql = "with s as (select kh.MaKH,count(hd.MaHD) as SoLanMua, sum(hd.TriGia - hd.SoTienGiam)as TriGia "
			 + "from HoaDon hd , KhachHang kh "
			 + "where hd.MaKH = kh.MaKH "
			 + "and hd.NgayLap between '" + ngayBatDau.toString()
			 + "' and '" + ngayKetThuc.toString()
			 + "'"
			 + "group by kh.MaKH )select top (" + soDong
			 + ") kh1.TenKH,kh1.DienThoai,s.* from s , KhachHang kh1 "
			 + "where s.MaKH = kh1.MaKH order by s.TriGia desc";

		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				int tsl = rs.getInt("SoLanMua");
				double tt = rs.getDouble("TriGia");
				int ma = rs.getInt("MaKH");
				String ten = rs.getString("TenKH");
				String dienthoai = rs.getString("DienThoai");
				KhachHangPhanLoai tmp = new KhachHangPhanLoai(tsl, tt, ma, ten, null, dienthoai);
				ds.add(tmp);
			}
		} catch (Exception ex) {
			Logger.getLogger(KhachHangDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return ds;

	}

	public static int laySoDong() {
		String sql = "select count(MaKH) as COU from KhachHang ";
		KetNoi cn = null;
		ResultSet rs = null;
		int n = 0;

		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				n = rs.getInt("COU");
			}
		} catch (Exception ex) {
			Logger.getLogger(KhachHangDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return n;
	}

	public static String layTenKH(int maKH) {
		String sql = "select TenKH from KhachHang where MaKH = " + maKH;
		KetNoi cn = null;
		ResultSet rs = null;
		String n = "";

		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				n = rs.getString("TenKH");
			}
		} catch (Exception ex) {
			Logger.getLogger(KhachHangDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return n;
	}

	public static ArrayList<KhachHang> layDanhSachPhanTrang(int soDong, int trangSo) {
		ArrayList<KhachHang> ds = new ArrayList<KhachHang>();

		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBangPhanTrang("KhachHang", "MaKh", soDong, trangSo);
			while (rs.next()) {
				int ma = rs.getInt("MaKH");
				String ten = rs.getString("TenKH");
				String diachi = rs.getString("DiaChi");
				String dienthoai = rs.getString("DienThoai");
				KhachHang tmp = new KhachHang(ma, ten, diachi, dienthoai);
				ds.add(tmp);
			}
		} catch (Exception ex) {
			Logger.getLogger(KhachHangDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}

	public static ArrayList<KhachHang> layKHDauTien() {
		ArrayList<KhachHang> ds = new ArrayList<KhachHang>();
		String sql = "SELECT top 1  MaKH  , TenKH, DiaChi, DienThoai   FROM  KhachHang ";

		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				int ma = rs.getInt("MaKH");
				String ten = rs.getString("TenKH");
				String diachi = rs.getString("DiaChi");
				String dienthoai = rs.getString("DienThoai");
				KhachHang tmp = new KhachHang(ma, ten, diachi, dienthoai);
				ds.add(tmp);
			}
		} catch (Exception ex) {
			Logger.getLogger(KhachHangDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}

	public static ArrayList<KhachHang> timKiemByTen(String tenKH) {
		ArrayList<KhachHang> ds = new ArrayList<KhachHang>();

		String sql = "SELECT * FROM KhachHang WHERE TenKH LIKE '%" + tenKH + "%'";
		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				int ma = rs.getInt("MaKH");
				String ten = rs.getString("TenKH");
				String diachi = rs.getString("DiaChi");
				String dienthoai = rs.getString("DienThoai");
				KhachHang tmp = new KhachHang(ma, ten, diachi, dienthoai);
				ds.add(tmp);
			}
		} catch (Exception ex) {
			Logger.getLogger(KhachHangDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}

	public static ArrayList<KhachHang> timKiem(KhachHang tmp) {
		ArrayList<KhachHang> ds = new ArrayList<KhachHang>();

		String sql = "";
		if (tmp.getMaKH() != 0) {
			sql = "SELECT * FROM KhachHang WHERE MaKH =" + tmp.getMaKH();
		} else if (tmp.getDienThoai().length() > 0) {
			sql = "SELECT * FROM KhachHang WHERE DienThoai LIKE '" + tmp.getDienThoai() + "%'";
		} else if (tmp.getDiaChi().length() > 0) {
			sql = "SELECT * FROM KhachHang WHERE DiaChi LIKE '%" + tmp.getDiaChi() + "%'";

		} else if (tmp.getTenKH().length() > 0) {
			sql = "SELECT * FROM KhachHang WHERE TenKH LIKE '" + tmp.getTenKH() + "%'";
		} else {
			sql = "Select top 10 [MaKH]"
				 + "      ,[TenKH]"
				 + "      ,[DiaChi]"
				 + "      ,[DienThoai] "
				 + "	from KhachHang"
				 + "	order by MaKH desc ";
		}


		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				int ma = rs.getInt("MaKH");
				String ten = rs.getString("TenKH");
				String diachi = rs.getString("DiaChi");
				String dienthoai = rs.getString("DienThoai");
				KhachHang t = new KhachHang(ma, ten, diachi, dienthoai);
				ds.add(t);
			}
		} catch (Exception ex) {
			Logger.getLogger(KhachHangDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}

	public static ArrayList<KhachHang> timKiemVoiSoLuongMua(KhachHang tmp) {
		ArrayList<KhachHang> ds = new ArrayList<KhachHang>();

		String sql = "";
		if (tmp.getMaKH() != 0) {
			sql = "SELECT KH.MaKH, KH.TenKH, KH.DienThoai ,COUNT(HD.MaHD)as SoLanMua "
				 + "FROM  KhachHang KH , HoaDon HD  "
				 + "where HD.MaKH = KH.MaKH  "
				 + "and KH.MaKH = " + tmp.getMaKH()
				 + " group by KH.MaKH, KH.TenKH, KH.DienThoai "
				 + "order by COUNT(HD.MaHD) desc;";
		} else if (tmp.getDienThoai().length() > 0) {
			sql = "SELECT KH.MaKH, KH.TenKH, KH.DienThoai ,COUNT(HD.MaHD)as SoLanMua "
				 + "FROM  KhachHang KH , HoaDon HD  "
				 + "where HD.MaKH = KH.MaKH  "
				 + "and (KH.DienThoai LIKE '" + tmp.getDienThoai() + "%' or KH.DienThoai LIKE '%" + tmp.getDienThoai() + "%') "
				 + "group by KH.MaKH, KH.TenKH, KH.DienThoai "
				 + "order by COUNT(HD.MaHD) desc;";
		} else if (tmp.getDiaChi().length() > 0) {
			sql = "SELECT KH.MaKH, KH.TenKH, KH.DienThoai ,COUNT(HD.MaHD)as SoLanMua "
				 + "FROM  KhachHang KH , HoaDon HD  "
				 + "where HD.MaKH = KH.MaKH  "
				 + "and (KH.DiaChi LIKE '" + tmp.getDiaChi() + "%' or KH.DiaChi LIKE '%" + tmp.getDiaChi() + "%') "
				 + "group by KH.MaKH, KH.TenKH, KH.DienThoai "
				 + "order by COUNT(HD.MaHD) desc;";
		} else if (tmp.getTenKH().length() > 0) {
			sql = "SELECT KH.MaKH, KH.TenKH, KH.DienThoai ,COUNT(HD.MaHD)as SoLanMua "
				 + "FROM  KhachHang KH , HoaDon HD  "
				 + "where HD.MaKH = KH.MaKH  "
				 + "and (KH.TenKH LIKE '" + tmp.getTenKH() + "%' or KH.TenKH LIKE '%" + tmp.getTenKH() + "%') "
				 + "group by KH.MaKH, KH.TenKH, KH.DienThoai "
				 + "order by COUNT(HD.MaHD) desc;";
		} else {
			sql = "Select top 10  KH.MaKH, KH.TenKH, KH.DienThoai ,COUNT(HD.MaHD)as SoLanMua FROM  KhachHang KH , HoaDon HD  where HD.MaKH = KH.MaKH  group by KH.MaKH, KH.TenKH, KH.DienThoai order  by COUNT(HD.MaHD) desc";
		}


		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				int ma = rs.getInt("MaKH");
				String ten = rs.getString("TenKH");
				String dienthoai = rs.getString("DienThoai");
				int soluong = rs.getInt("SoLanMua");
				KhachHang t = new KhachHang(ma, ten, dienthoai, soluong);
				ds.add(t);
			}
		} catch (Exception ex) {
			Logger.getLogger(KhachHangDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}

	public static boolean them(KhachHang kh) {
		boolean kq = false;

		KetNoi cn = null;
		int n;
		n = -1;
		try {
			cn = new KetNoi();
			Object[] ob = new Object[3];
			ob[0] = kh.getTenKH();
			ob[1] = kh.getDiaChi();
			ob[2] = kh.getDienThoai();

			n = cn.thucThiProc("spThemKhachHang", ob);
		} catch (SQLException ex) {
			Logger.getLogger(KhachHangDao.class.getName()).log(Level.SEVERE, null, ex);
			//ai viet cai ham nay lam on cap nhat ma khach hang lai di
		}
		if (n == 1) {
			kq = true;
		}
		cn.dongKetNoi();
		return kq;
	}

	public static boolean xoa(int maKH) {
		boolean kq = false;
		String str = "if not exists (select hd.MaKH from HoaDon hd where hd.MaKH =" + maKH + " )";
		String sql = String.format(str + " DELETE FROM KhachHang WHERE MaKH = %d", maKH);
		KetNoi cn = null;
		int n;
		n = -1;
		try {
			cn = new KetNoi();
			n = cn.thucThi(sql);
		} catch (SQLException ex) {
			Logger.getLogger(KhachHangDao.class.getName()).log(Level.SEVERE, null, ex);
		}
		if (n == 1) {
			kq = true;
		}
		cn.dongKetNoi();
		return kq;
	}

	public static boolean capNhat(KhachHang tmp) {
		boolean kq = false;
		String sql = String.format("UPDATE KhachHang "
			 + "SET "
			 + "TenKH = N'%s', "
			 + "DiaChi = N'%s',"
			 + "DienThoai = '%s' "
			 + "WHERE  MaKH = %d; ", tmp.getTenKH(), tmp.getDiaChi(), tmp.getDienThoai(), tmp.getMaKH());
		KetNoi cn = null;
		int n;
		n = -1;
		try {
			cn = new KetNoi();
			n = cn.thucThi(sql);
		} catch (SQLException ex) {
			Logger.getLogger(KhachHangDao.class.getName()).log(Level.SEVERE, null, ex);
		}
		if (n == 1) {
			kq = true;
		}
		cn.dongKetNoi();
		return kq;
	}

	public static KhachHang layByMa(int maKH) {
		KhachHang tmp = null;
		KetNoi cn = null;
		try {
			String sql = "SELECT * FROM KhachHang WHERE MaKH =" + maKH;
			cn = new KetNoi();
			ResultSet rs = cn.docBang(sql);
			if (rs.next()) {
				int ma = rs.getInt("MaKH");
				String ten = rs.getString("TenKH");
				String diachi = rs.getString("DiaChi");
				String dienthoai = rs.getString("DienThoai");
				tmp = new KhachHang(ma, ten, diachi, dienthoai);
			}
		} catch (Exception ex) {
			Logger.getLogger(KhachHangDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}

		return tmp;
	}
}
